package cn.iocoder.yudao.module.data.service.dscategory;

import cn.hutool.db.DbUtil;
import cn.hutool.db.sql.SqlUtil;
import org.jetbrains.annotations.Nullable;
import org.slf4j.Logger;
import org.springframework.stereotype.Service;
import jakarta.annotation.Resource;
import org.springframework.validation.annotation.Validated;

import java.sql.*;
import java.util.*;
import cn.iocoder.yudao.module.data.controller.admin.dscategory.vo.*;
import cn.iocoder.yudao.module.data.dal.dataobject.dscategory.DsCategoryDO;
import cn.iocoder.yudao.framework.common.pojo.PageResult;
import cn.iocoder.yudao.framework.common.util.object.BeanUtils;

import cn.iocoder.yudao.module.data.dal.mysql.dscategory.DsCategoryMapper;
import org.slf4j.LoggerFactory;

import static cn.iocoder.yudao.framework.common.exception.util.ServiceExceptionUtil.exception;
import static cn.iocoder.yudao.module.data.enums.ErrorCodeConstants.*;

/**
 * 分类 Service 实现类
 *
 * @author 芋道源码
 */
@Service
@Validated
public class DsCategoryServiceImpl implements DsCategoryService {

    @Resource
    private DsCategoryMapper dsCategoryMapper;

    Logger logger = LoggerFactory.getLogger(DsCategoryServiceImpl.class);


    @Override
    public Long createDsCategory(DsCategorySaveReqVO createReqVO) {
        // 插入
        DsCategoryDO dsCategory = BeanUtils.toBean(createReqVO, DsCategoryDO.class);
        dsCategoryMapper.insert(dsCategory);
        // 返回
        return dsCategory.getId();
    }

    @Override
    public void updateDsCategory(DsCategorySaveReqVO updateReqVO) {
        // 校验存在
        validateDsCategoryExists(updateReqVO.getId());
        // 更新
        DsCategoryDO updateObj = BeanUtils.toBean(updateReqVO, DsCategoryDO.class);
        dsCategoryMapper.updateById(updateObj);
    }

    @Override
    public void deleteDsCategory(Long id) {
        // 校验存在
        validateDsCategoryExists(id);
        // 删除
        dsCategoryMapper.deleteById(id);
    }

    private void validateDsCategoryExists(Long id) {
        if (dsCategoryMapper.selectById(id) == null) {
            throw exception(DS_CATEGORY_NOT_EXISTS);
        }
    }

    @Override
    public DsCategoryDO getDsCategory(Long id) {
        return dsCategoryMapper.selectById(id);
    }

    @Override
    public PageResult<DsCategoryDO> getDsCategoryPage(DsCategoryPageReqVO pageReqVO) {
        return dsCategoryMapper.selectPage(pageReqVO);
    }


    @Override
    public List<DsCategoryDO> getDsList(DsCategoryListReqVO reqVO) {
        List<DsCategoryDO> list = dsCategoryMapper.selectList();
//        list.sort(Comparator.comparing(DsCategoryDO::));
        return list;
    }

    @Override
    public List<DsTableRespVO> getTableList(DsTableListPageReqVO reqVO) {
        Long dsId = reqVO.getDsId();
        if (dsId != null) {
            //根据数据库ID获取数据库信息
            DsCategoryDO dsCategoryDO = dsCategoryMapper.selectOne("id", dsId);
            //MYSQL的连接信息
            String configuration = dsCategoryDO.getConfiguration();
            //根据连接信息获取表信息,configuration包含mysql的URL连接信息
            // 获取数据库连接
            return getDsTableRespVOS(configuration);
        }

        return List.of();
    }

    /**
     * 根据数据库ID获取表信息
     * @param dsId
     * @return
     */
    @Override
    public List<DsTableRespVO> getTableList(Long dsId) {
        //根据数据库ID获取数据库信息
        DsCategoryDO dsCategoryDO = dsCategoryMapper.selectOne("id", dsId);
        //MYSQL的连接信息
        String configuration = dsCategoryDO.getConfiguration();
        //根据连接信息获取表信息,configuration包含mysql的URL连接信息
        // 获取数据库连接
        return getDsTableRespVOS(configuration);
    }


    public static void main(String[] args) {
        String configuration = "jdbc:mysql://192.168.10.128:3306/ruoyi-plus-v1.0?user=root&password=123456&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&useSSL=false";
        DsCategoryServiceImpl dsCategoryService = new DsCategoryServiceImpl();
        List<DsTableRespVO> dsTableRespVOS = dsCategoryService.getDsTableRespVOS(configuration);
        System.out.println(dsTableRespVOS);
    }

    /**
     * 根据连接信息获取表信息
     * @param configuration
     * @return
     */
    @Nullable
    private List<DsTableRespVO> getDsTableRespVOS(String configuration) {
        List<DsTableRespVO> tableList = null;
        Connection connection = null;
        ResultSet resultSet = null;
        try {
            //通过ip，port，database，user，password获取连接
            connection = DriverManager.getConnection(configuration);

            //根据configuration获取数据库名称,configuration = jdbc:mysql://192.168.10.128:3306/ruoyi-plus-v1.0?user=root&password=123456
            String schema  = configuration.split("/")[3].split("\\?")[0];

            DatabaseMetaData metaData = connection.getMetaData();

            // 获取所有表信息
            ResultSet tables = metaData.getTables(schema, null, "%", new String[]{"TABLE"});
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                String remarks = tables.getString("REMARKS"); // 表描述/注释
                DsTableRespVO dsTableRespVO = new DsTableRespVO();
                dsTableRespVO.setTableName(tableName);
                dsTableRespVO.setTableDescription(remarks);
                if (tableList == null) {
                    tableList = new ArrayList<>();
                }
                tableList.add(dsTableRespVO);
            }
        } catch (SQLException e) {
            logger.error(e.getMessage());
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return tableList;
    }

}